﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using NTTDATA.BEENChina.ResultCertification.Checker;
using NTTDATA.BEENChina.ResultCertification.EntryModel;
using NTTDATA.BEENChina.ResultCertification.Result;
using Microsoft.ApplicationBlocks.Data;

namespace NTTDATA.BEENChina.ResultCertification.DataStore
{
    public class EntryClientAccess :IEntryClient
    {
        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.AppSettings["ResultCertificationDB"];
            }
        }
        
        public bool VerfityOperator(string id, string pass)
        {

            bool bresult = false;

            string sql = "SELECT Count(*) FROM  [User]  " +
                         "WHERE [UserID] =   @UserID AND   [PassWord] = @PassWord";

            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@UserID", id),
                    new SqlParameter("@PassWord", pass),
                };

            try
            {
                string count = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, sql, sqlParameters).ToString();

                int rowCount = int.Parse(count);

                bresult = rowCount > 0;
            }
            catch (Exception ex)
            {

                Log.Console.Error("EntryClientAccess.VerfityOperator", ex);

            }


            return bresult;

        }

        public int SetClient(string id, string ipAddress)
        {
            int effactRow = 0;

            string sql = " UPDATE [User]  " +
                         " SET [clientAddress] = @address"+
                         " WHERE [UserID] =   @UserID";

            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@UserID", id),
                    new SqlParameter("@address", ipAddress),
                };

            if (string.IsNullOrEmpty(ipAddress))
            {
                sqlParameters[1].Value = DBNull.Value;
            }

            try
            {
                effactRow = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);
                 
                
            }
            catch (Exception ex)
            {

                Log.Console.Error("EntryClientAccess.SetClient", ex);

            }


            return effactRow;

        }

        public List<string> GetDistinctOrderReceiveDates()
        {
            string sql = "  SELECT DISTINCT  A.OridReceiveDate	[OridReceiveDate] " +
                         "  FROM  [VerfityJob] A	" +
                         "  WHERE A.[DownLoadTime] IS NULL ";

            List<string> orderReceiveDates = null;
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql))
                {
                    while (reader.Read())
                    {
                        if (orderReceiveDates == null)
                            orderReceiveDates = new List<string>();

                        orderReceiveDates.Add(reader["OridReceiveDate"].ToString());
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("EntryClientAccess.GetDistinctOrderReceiveDates", ex);
            }

            return orderReceiveDates;

        }

        public List<VerfityJobListItem> GetJobList(string orderReceiveDate,string categoryType)
        {
            string sql = " SELECT   A.[VerfityItemID] " +
                         "          ,B.[VerfityItemName]  " +
                         "          ,A.[CategoryID]   " +
                         "          ,A.[CategoryName]   " +
                         "          ,SUM(ImageCount) ImageCount      " +
                         " FROM  [VerfityJob] A INNER JOIN [VerfityItem] B ON " +
                         "      A.[VerfityItemID] = B.[VerfityItemID]" +
                         " WHERE A.[DownLoadTime] IS NULL AND " +
                         "      A.[OridReceiveDate] = @OridReceiveDate AND" +
                         "      B.CategoryType = @CategoryType " +
                         " GROUP BY A.[VerfityItemID] ,B.[VerfityItemName],A.[CategoryName],A.[CategoryID] " +
                         " ORDER BY A.[VerfityItemID] ,B.[VerfityItemName],A.[CategoryName],A.[CategoryID]  ";


            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@OridReceiveDate", orderReceiveDate) ,
                    new SqlParameter("@CategoryType", categoryType) 

                };

            List<VerfityJobListItem> jobList = null;
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql,sqlParameters ))
                {
                    while (reader.Read())
                    {
                        if (jobList == null)
                            jobList = new List<VerfityJobListItem>();

                        jobList.Add(
                            new VerfityJobListItem() { 
                                VerfityItemID = reader["VerfityItemID"].ToString(),
                                VerfityItemName = reader["VerfityItemName"].ToString(),
                                ImageCount = reader["ImageCount"].ToString(),
                                CategoryID = reader["CategoryID"].ToString(),
                                CategoryName = reader["CategoryName"].ToString()
                            }
                        );
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("EntryClientAccess.GetJobList", ex);
            }

            return jobList;
        } 
       
        public int UpdateChildImageVerfityJobId(string verfityJobId, List<string> seqnos)
        {
            int effactRow = 0;

            foreach (string seqno in seqnos)
            {
                string sql = "UPDATE [ChildImages]   SET [VerfityJobID] = @VerfityJobID      WHERE ID = @id";

                SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@id", seqno),
                    new SqlParameter("@VerfityJobID", verfityJobId), 

                };
                try
                {
                    effactRow += SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);


                }
                catch (Exception ex)
                {

                    Log.Console.Error("EntryClientAccess.VerfityOperator", ex);

                }

            }

            return effactRow;
        }

        public VerfityJobItem DownLoadJob(string opid, string verfityItemID, string orderReceiveDate)
        {
            VerfityJobItem verfityJobItem = null ;
            ;
 
            string sqlGetJobId = " SELECT TOP 1  [VerfityJobID] " +
                                 " FROM	[VerfityJob] " +
                                 " WHERE [VerfityItemID] = @VerfityItemID AND  " +
                                 "       [DownLoadTime] IS NULL AND " +
                                 "       [OridReceiveDate] = @OridReceiveDate ";

            SqlParameter[] sqlParametersGetJob =
                {
                    new SqlParameter("@VerfityItemID", verfityItemID) ,
                    new SqlParameter("@OridReceiveDate", orderReceiveDate) 

                };

            object verfityJob = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, sqlGetJobId,
                                                        sqlParametersGetJob);

            string verfityJobId = string.Empty;

            if (verfityJob !=null)
             verfityJobId = verfityJob.ToString();


            if (!String.IsNullOrEmpty(verfityJobId))
            {
               
                 verfityJobItem = new VerfityJobItem();

                verfityJobItem.VerfityJobID = verfityJobId;
                verfityJobItem.VerfityItemID = verfityItemID;

                IVerfity verfityAccess = new VerfityAccess();
                var verItem = verfityAccess.GetVerfityItem(verfityItemID);

                verfityJobItem.VerfityItemName = verItem.VerfityItemName;

                //保留下载状态
                string sqlUpdateJobId = "   UPDATE	[VerfityJob] " +
                                        "   SET DownLoadTime =  @DownLoadTime ,UserID = @UserID" +
                                        "   WHERE [VerfityJobID] = @VerfityJobID ";

                SqlParameter[] sqlParametersUpJob =
                {
                    new SqlParameter("@VerfityJobID", verfityJobId),
                    new SqlParameter("@DownLoadTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), 
                    new SqlParameter("@UserID", opid), 
 
                };

                int effactRow = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sqlUpdateJobId, sqlParametersUpJob);

                if (effactRow > 0)
                {

                    verfityJobItem.ChildImage = GetChildImageObjects(verfityJobId);
                }

            }

            return verfityJobItem;
        }

        public VerfityJobItem DownLoadJob(string opid, string verfityItemID, string categoryID, string orderReceiveDate)
        {
            VerfityJobItem verfityJobItem = null;
            ;

            string sqlGetJobId = " SELECT TOP 1  [VerfityJobID] " +
                                 " FROM	[VerfityJob] " +
                                 " WHERE [VerfityItemID] = @VerfityItemID AND  " +
                                 "       [DownLoadTime] IS NULL AND " +
                                 "       [OridReceiveDate] = @OridReceiveDate AND" +
                                 "       [CategoryID]   = @CategoryID ";

            SqlParameter[] sqlParametersGetJob =
                {
                    new SqlParameter("@VerfityItemID", verfityItemID) ,
                    new SqlParameter("@OridReceiveDate", orderReceiveDate) ,
                    new SqlParameter("@CategoryID", categoryID) 
                };

            object verfityJob = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, sqlGetJobId,
                                                        sqlParametersGetJob);

            string verfityJobId = string.Empty;

            if (verfityJob != null)
                verfityJobId = verfityJob.ToString();


            if (!String.IsNullOrEmpty(verfityJobId))
            {

                verfityJobItem = new VerfityJobItem();

                verfityJobItem.VerfityJobID = verfityJobId;
                verfityJobItem.VerfityItemID = verfityItemID;
                verfityJobItem.CategoryID = categoryID;

                IVerfity verfityAccess = new VerfityAccess();
                var verItem = verfityAccess.GetVerfityItem(verfityItemID);

                verfityJobItem.VerfityItemName = verItem.VerfityItemName;

                //保留下载状态
                string sqlUpdateJobId = "   UPDATE	[VerfityJob] " +
                                        "   SET DownLoadTime =  @DownLoadTime ,UserID = @UserID" +
                                        "   WHERE [VerfityJobID] = @VerfityJobID  AND" +
                                        "       [CategoryID]   = @CategoryID ";

                SqlParameter[] sqlParametersUpJob =
                {
                    new SqlParameter("@VerfityJobID", verfityJobId),
                    new SqlParameter("@CategoryID", categoryID) ,
                    new SqlParameter("@DownLoadTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")), 
                    new SqlParameter("@UserID", opid), 
 
                };

                int effactRow = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sqlUpdateJobId, sqlParametersUpJob);

                if (effactRow > 0)
                {

                    verfityJobItem.ChildImage = GetChildImageObjects(verfityJobId);
                }

            }

            return verfityJobItem;
        }


        private List<ChildImage> GetChildImageObjects(string verfityJobID)
        {

            string sql = "SELECT    " +
                        " [ID],[OrderID],[ImageID],[FormID] " +
                        " ,[FormName],[CustomerID],[FieldID],[FieldName] " +
                        " ,[CategoryID],[CategoryName] ,[StoreTableMame]" +
                        " ,[AOpID],[AResult],[AEntryCommentID],[ABJobId] " +
                        " ,[BOpID],[BResult] ,[BEntryCommentID]  " +
                        " ,[COpID],[CResult],[CEntryCommentID],[CJobID] " +
                        " ,[DOpID],[DResult],[DEntryCommentID],[DJobID] " +
                        " ,[EOpID],[EResult],[EJobID]  " +
                        " ,[FinalResult],[FinalEntryCommentID],[VerfityItemID] " +
                        " ,[EntryRule] " +
                        " ,[VerfityJobID],[VerfityTime],[VerfityResult] " +
                        " ,[VerfityComment],[MissType],[MissWord] " +
                         ",[VerfityProperties] " +
                        " FROM [ChildImages]  " +
                        "  WHERE  [VerfityJobID] =   @VerfityJobID ";

            SqlParameter[] sqlParameters = 
                { 
                    new SqlParameter("@VerfityJobID", verfityJobID)
                };

            return AssembleChildImage(sql, sqlParameters);
        }

        public List<ChildImage> GetNgChildImageObjects()
        {

            string sql = "SELECT    " +
                        " [ID],[OrderID],[ImageID],[FormID] " +
                        " ,[FormName],[CustomerID],[FieldID],[FieldName] " +
                        " ,[CategoryID],[CategoryName] ,[StoreTableMame]" +
                        " ,[AOpID],[AResult],[AEntryCommentID],[ABJobId] " +
                        " ,[BOpID],[BResult] ,[BEntryCommentID]  " +
                        " ,[COpID],[CResult],[CEntryCommentID],[CJobID] " +
                        " ,[DOpID],[DResult],[DEntryCommentID],[DJobID] " +
                        " ,[EOpID],[EResult],[EJobID]  " +
                        " ,[FinalResult],[FinalEntryCommentID],[VerfityItemID] " +
                        " ,[EntryRule] " +
                        " ,[VerfityJobID],[VerfityTime],[VerfityResult] " +
                        " ,[VerfityComment],[MissType],[MissWord],[VerfityProperties] " +
                        " FROM [ChildImages]  " +
                        " Where VerfityResult = 2  AND [AOpID] IS NULL ";

            
            return AssembleChildImage(sql, null);
        }

        private List<ChildImage> AssembleChildImage(string sql, SqlParameter[] sqlParameters)
        {
            List<ChildImage> imagesChilds = new List<ChildImage>();
            try
            {
                using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
                {
                    while (reader.Read())
                    {
                        VerfityConfirmResult confirmResult = new VerfityConfirmResult
                        {
                            Result = reader["VerfityResult"] == DBNull.Value ? string.Empty : reader["VerfityResult"].ToString(),
                            VerfityComment = reader["VerfityComment"] == DBNull.Value ? string.Empty : reader["VerfityComment"].ToString(),
                            MissWord = reader["MissWord"] == DBNull.Value ? 0 : int.Parse(reader["MissWord"].ToString()),
                            VerfityTime = reader["VerfityTime"] == DBNull.Value ? 0 : int.Parse(reader["VerfityTime"].ToString()),
                            MissType = reader["MissType"] == DBNull.Value ? EnumMissType.None : (EnumMissType)int.Parse(reader["MissType"].ToString()),
                        };

                        ChildImage child = new ChildImage()
                        {
                            SeqNo = reader["ID"].ToString(),
                            OrderId = reader["OrderID"].ToString(),
                            ImageId = reader["ImageID"].ToString(),
                            FormId = reader["FormID"].ToString(),
                            FormName = reader["FormName"].ToString(),
                            CustomerId = reader["CustomerID"].ToString(),
                            FieldId = reader["FieldID"].ToString(),
                            FieldName = reader["FieldName"].ToString(),
                            CategoryId = reader["CategoryID"].ToString(),
                            CategoryName = reader["CategoryName"].ToString(),
                            StoreTableName = reader["StoreTableMame"].ToString(),
                           
                            VerfityConfirmResult = confirmResult,
                            EntryRule = reader["EntryRule"].ToString()
                        };

                        child.Reason.VerfityItemId = reader["VerfityItemID"].ToString();

                        if (reader["ABJobId"] != DBNull.Value)
                        {
                            child.OperatorEntryResults.Add(new OpEntryResult()
                            {
                                Rank = EnumRank.A,
                                EntryResult = reader["AResult"].ToString(),
                                EntryCommon = reader["AEntryCommentID"].ToString(),
                                OperatorId = reader["AOpID"].ToString(),
                                JobId = reader["ABJobId"].ToString()
                            });

                            child.OperatorEntryResults.Add(new OpEntryResult()
                            {
                                Rank = EnumRank.B,
                                EntryResult = reader["BResult"].ToString(),
                                EntryCommon = reader["BEntryCommentID"].ToString(),
                                OperatorId = reader["BOpID"].ToString(),
                                JobId = reader["ABJobId"].ToString()
                            });
                        }
                        if (reader["CJobID"] != DBNull.Value)
                        {
                            child.OperatorEntryResults.Add(new OpEntryResult()
                            {
                                Rank = EnumRank.C,
                                EntryResult = reader["CResult"].ToString(),
                                EntryCommon = reader["CEntryCommentID"].ToString(),
                                OperatorId = reader["COpID"].ToString(),
                                JobId = reader["CJobID"].ToString()
                            });
                        }
                        if (reader["DJobID"] != DBNull.Value)
                        {
                            child.OperatorEntryResults.Add(new OpEntryResult()
                            {
                                Rank = EnumRank.D,
                                EntryResult = reader["DResult"].ToString(),
                                EntryCommon = reader["DEntryCommentID"].ToString(),
                                OperatorId = reader["DOpID"].ToString(),
                                JobId = reader["DJobID"].ToString()
                            });
                        }
                        if (reader["EJobID"] != DBNull.Value)
                        {
                            child.OperatorEntryResults.Add(new OpEntryResult()
                            {
                                Rank = EnumRank.E,
                                EntryResult = reader["EResult"].ToString(),
                                OperatorId = reader["EOpID"].ToString(),
                                JobId = reader["EJobID"].ToString()
                            });
                        }

                        child.OperatorEntryResults.Add(new OpEntryResult()
                        {
                            Rank = EnumRank.Final,
                            EntryResult = reader["FinalResult"].ToString(),
                            EntryCommon = reader["FinalEntryCommentID"].ToString()
                        });

                        if (reader["VerfityProperties"] != DBNull.Value)
                        {
                            var instance = reader["VerfityProperties"].ToString();
                            var properties =
                                 new Util.Serializer().DeSerializeXml(typeof(VerfityProperties), instance) as VerfityProperties;
                            child.Reason = properties;
                        }

                        imagesChilds.Add(child);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Console.Error("EntryClientAccess.GetChildImageObjects", ex);
            }

            return imagesChilds;            
        }

        public int UpLoadJob(string opid, VerfityJobItem verfityJobItem)
        {
            int effactRow = 0;

            string sql = "  UPDATE [VerfityJob] " +
                         "  SET [UpLoadTime] = @UpLoadTime ," +
                         "      [UserID] = @UserID " +
                         "  WHERE VerfityJobID = @VerfityJobID AND " +
                         "      CategoryId = @CategoryId ";

            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@UpLoadTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")) ,
                    new SqlParameter("@VerfityJobID", verfityJobItem.VerfityJobID) ,
                    new SqlParameter("@UserID", opid) ,
                    new SqlParameter( "@CategoryId",verfityJobItem.CategoryID)

                };

            effactRow = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);

            //画像 以及结果的更新 在更新job前已更新完
          //  effactRow += UploadVerfityConfirmResult(verfityJobItem.ChildImage);

            return effactRow;
        }
        
        ///// <summary>
        ///// 更新子画像的确认结果
        ///// </summary>
        ///// <param name="childImages"></param>
        ///// <returns></returns>
        //private int UploadVerfityConfirmResult(IEnumerable<ChildImage> childImages)
        //{
        //    int effactRow = 0;
        //    if (childImages != null)
        //    {   
        //        foreach (var childImage in childImages)
        //        {

        //            effactRow += UploadVerfityConfirmResultAndOpid(childImage);
        //        }
        //    }

        //    return effactRow;
        //}

        //public   int UploadVerfityConfirmResultAndOpid(ChildImage childImage)
        //{
        //    int effactRow = 0;
        //    string sql = "  UPDATE [ChildImages]  " +
        //                 "    SET " +
        //                 "       , [AOpID] = @AOpID	 " +
        //                 "       , [BOpID]= @BOpID	 " +
        //                 "       , [COpID] = @COpID	 " +
        //                 "       , [DOpID] = @DOpID	 " +
        //                 "       , [EOpID] = @EOpID	 " +
        //                 "       ,[VerfityTime] = @VerfityTime    " +
        //                 "       ,[VerfityResult] = @VerfityResult   " +
        //                 "       ,[VerfityComment] = @VerfityComment " +
        //                 "       ,[MissType] = @MissType " +
        //                 "       ,[MissWord] = @MissWord " +
        //                 "       ,[VerfityProperties]  = @VerfityProperties " +
        //                 "    WHERE ID = @id ";

        //    SqlParameter[] sqlParameters =
        //        {
        //            new SqlParameter("@VerfityTime", childImage.VerfityConfirmResult.VerfityTime),
        //            new SqlParameter("@VerfityResult", childImage.VerfityConfirmResult.Result),
        //            new SqlParameter("@VerfityComment", childImage.VerfityConfirmResult.VerfityComment),
        //            new SqlParameter("@MissType", childImage.VerfityConfirmResult.MissType),
        //            new SqlParameter("@MissWord", childImage.VerfityConfirmResult.MissWord),
        //            new SqlParameter("@id", childImage.SeqNo),
        //            new SqlParameter("@VerfityProperties", DBNull.Value)
        //        };

        //    if (childImage.Reason != null)
        //    {
        //        string reasonXml = new Util.Serializer().SerializeXml(typeof (VerfityProperties), childImage.Reason);
        //        sqlParameters.Single(t => t.ParameterName == "@VerfityProperties").Value = reasonXml;
        //    }
        //    effactRow  = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);
        //    return effactRow;
        //}

        public int CancelJob(string opid, string verfityJobID, string categoryID)
        {
            SetClient(opid, string.Empty);

            //保留下载状态
            string sql =    "   UPDATE	[VerfityJob] " +
                            "   SET DownLoadTime =  null," +
                            "       [UpLoadTime] = null,  " +
                            "       [UserID] = null " +
                            "   WHERE [VerfityJobID] = @VerfityJobID AND " +
                            "         [UserID] = @UserID AND" +
                            "      CategoryId = @CategoryId ";

            SqlParameter[] sqlParameters =
                {
                    new SqlParameter("@UpLoadTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")) ,
                    new SqlParameter("@VerfityJobID", verfityJobID) ,
                    new SqlParameter("@UserID", opid) ,
                    new SqlParameter( "@CategoryId",categoryID)
                };

            int effactRow = SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, sql, sqlParameters);

            return effactRow;
        }

       
    }
}
